﻿Imports System.Data.OleDb

Module XL_ChuyenKhoan
    Structure ChuyenKhoan
        Private _iD As Integer
        Property ID() As Integer
            Get
                Return _iD
            End Get
            Set(ByVal value As Integer)
                _iD = value
            End Set
        End Property
        Private _nguoiDung As Integer
        Property NguoiDung() As Integer
            Get
                Return _nguoiDung
            End Get
            Set(ByVal value As Integer)
                _nguoiDung = value
            End Set
        End Property
        Private _ngay As Date
        Property Ngay() As Date
            Get
                Return _ngay
            End Get
            Set(ByVal value As Date)
                _ngay = value
            End Set
        End Property
        Private _tKChuyen As Integer
        Property TKChuyen() As Integer
            Get
                Return _tKChuyen
            End Get
            Set(ByVal value As Integer)
                _tKChuyen = value
            End Set
        End Property
        Private _tKNhan As Integer
        Property TKNhan() As Integer
            Get
                Return _tKNhan
            End Get
            Set(ByVal value As Integer)
                _tKNhan = value
            End Set
        End Property
        Private _soTien As Integer
        Property SoTien() As Integer
            Get
                Return _soTien
            End Get
            Set(ByVal value As Integer)
                _soTien = value
            End Set
        End Property
        Private _lyDo As String
        Property LyDo() As String
            Get
                Return _lyDo
            End Get
            Set(ByVal value As String)
                _lyDo = value
            End Set
        End Property
    End Structure

    Public Function LayDS(ByVal user As Integer) As List(Of ChuyenKhoan)
        Dim ds As New List(Of ChuyenKhoan)
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from ChuyenKhoan where NguoiDung=@NguoiDung", con)
            cmd.Parameters.AddWithValue("@NguoiDung", user)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim p As ChuyenKhoan = New ChuyenKhoan()
                p.ID = rd.GetInt32(0)
                p.NguoiDung = rd.GetInt32(1)
                p.Ngay = rd.GetDateTime(2)
                p.TKChuyen = rd.GetInt32(3)
                p.TKNhan = rd.GetInt32(4)
                p.SoTien = rd.GetInt32(5)
                p.LyDo = rd.GetString(6)
                ds.Add(p)
            End While
            con.Close()
            Return (ds)
        Catch ex As Exception
            Return ds
        End Try
    End Function

    Public Function Lay(ByVal id As Integer) As ChuyenKhoan
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from ChuyenKhoan where ID=@ID", con)
            cmd.Parameters.AddWithValue("@ID", id)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            Dim p As ChuyenKhoan = New ChuyenKhoan()
            If rd.Read() Then
                p.ID = rd.GetInt32(0)
                p.NguoiDung = rd.GetInt32(1)
                p.Ngay = rd.GetDateTime(2)
                p.TKChuyen = rd.GetInt32(3)
                p.TKNhan = rd.GetInt32(4)
                p.SoTien = rd.GetInt32(5)
                p.LyDo = rd.GetString(6)
            End If
            con.Close()
            Return p
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Them(ByRef p As ChuyenKhoan) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("insert into ChuyenKhoan(NguoiDung, Ngay, TKChuyen, TKNhan, SoTien, LyDo) values(@NguoiDung, @Ngay, @TKChuyen, @TKNhan, @SoTien, @LyDo)", con)
            cmd.Parameters.AddWithValue("@NguoiDung", p.NguoiDung)
            cmd.Parameters.AddWithValue("@Ngay", p.Ngay)
            cmd.Parameters.AddWithValue("@TKChuyen", p.TKChuyen)
            cmd.Parameters.AddWithValue("@TKNhan", p.TKNhan)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@LyDo", p.LyDo)
            Dim kq = cmd.ExecuteNonQuery()
            cmd.CommandText = "select @@IDENTITY"
            p.ID = cmd.ExecuteScalar()

            'Cap nhat cac tai khoan
            Dim tk1 = XL_TaiKhoan.Lay(p.TKChuyen)
            tk1.SoDu -= p.SoTien
            XL_TaiKhoan.CapNhat(tk1)

            Dim tk2 = XL_TaiKhoan.Lay(p.TKNhan)
            tk2.SoDu += p.SoTien
            XL_TaiKhoan.CapNhat(tk2)

            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Xoa(ByVal p As ChuyenKhoan) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("delete from ChuyenKhoan where ID = @ID", con)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function CapNhat(ByVal p As ChuyenKhoan) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("update ChuyenKhoan set NguoiDung=@NguoiDung, Ngay=@Ngay, TKChuyen=@TKChuyen, TKNhan=@TKNhan, SoTien=@SoTien, LyDo=@LyDo where ID = @ID", con)
            cmd.Parameters.AddWithValue("@NguoiDung", p.NguoiDung)
            cmd.Parameters.AddWithValue("@Ngay", p.Ngay)
            cmd.Parameters.AddWithValue("@TKChuyen", p.TKChuyen)
            cmd.Parameters.AddWithValue("@TKNhan", p.TKNhan)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@LyDo", p.LyDo)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function
End Module
